import type { Router } from "express"
import type { Ioracle, IcallbackFn } from "../../types"
import { decrypt } from "../../utils/privateKey"
import { SendMailLoop } from "./interview"
import fs from "fs";
import multer from "multer";

const storage = multer.diskStorage({
  //上传文件到服务器的存储位置
  destination: "../uploadFile/hr_improve_achieve/appendix",
  filename: function (_req, _file, callback) {
    // 获取时间戳
    var filename = new Date().getTime();
    // 文件的命名为：时间戳 + 文件附件名 + 文件的后缀名
    callback(null, filename + "-tempname" + ".png");
  },
});
const upload = multer({
  storage: storage,
});

export default (router: Router, oracle: Ioracle, callbackFn: IcallbackFn) => {
  //獲取當前任務
  router.post("/getTask", (req, res) => {
    let { param } = req.body
    param = JSON.parse(param)
    let sql =
      param.type === "count" ? "select count(*)count from (" : " select * from("
    sql +=
      " select row_number() over (order by created_date desc) as rowseq,x.* from ("
    sql +=
      " select a.docid,a.badge,d.chinese_name,to_char(a.created_date,'yyyy-mm-dd')created_date,b.flow_phase,b.flow_code"
    sql +=
      " from xxhr_improve_achieve_header a,xxhr_improve_achieve_flow b,user_info_n c,user_info_n d"
    sql +=
      " where a.docid = b.docid and a.current_flow = b.flow and a.status = 'N' and b.klm=c.account and b.approve is null and a.badge = d.work_num"
    sql += ` and b.klm = '${decrypt(param.username)}')x)`
    if (param.type !== "count") {
      sql += ` where rowseq between ${
        (param.current - 1) * param.pagesize + 1
      } and ${param.current * param.pagesize}`
    }
    callbackFn(res, oracle.connHrm, { sql })
  })

  router.post("/managerinfo", (req, res) => {
    const { docid, username } = req.body
    let sql =
      " select b.flow_code,b.flow_phase,a.is_improve from xxhr_improve_achieve_header a,xxhr_improve_achieve_flow b"
    sql += ` where a.docid = b.docid and a.current_flow = b.flow and a.docid = '${docid}' and b.klm = '${decrypt(
      username
    )}'`
    callbackFn(res, oracle.connHrm, { sql })
  })

  router.post("/sendapprove", async (req, res) => {
    const { docid, username, comments, approve } = req.body
    let sql = ` begin xxhr_improve_achieve_approve_p('${docid}','${decrypt(
      username
    )}','${comments}','${approve}',0);end;`
    try {
      await oracle.connHrm({ sql })
      sql =
        " select b.docid,b.flow_phase,substr(b.klm,9) || '@compeq.huizhou.gd.cn' email,c.chinese_name "
      sql +=
        " ,case when b.flow_code like '%_sign_%' then 'Y' else 'N' end as sign_flag"
      sql +=
        " from xxhr_improve_achieve_header a,xxhr_improve_achieve_flow b,user_info_n c"
      sql += ` where a.docid = b.docid and a.current_flow = b.flow  and b.klm = c.account and a.docid = '${docid}'`
      const resMail = await oracle.connHrm({ sql })
      await SendMailLoop(resMail)
      res.send({ code: 200, message: "success", data: [] })
    } catch {
      res.send({ code: 0, message: "系統異常" })
    }
  })

  router.post("/getquerylist", (req, res) => {
    let { param } = req.body
    param = JSON.parse(param)
    let sql =
      param.type === "count" ? "select count(*)count from (" : " select * from("
    sql +=
      " select row_number() over (order by created_date desc) as rowseq,x.* from ("
    sql +=
      " select a.docid,a.badge,to_char(a.created_date,'yyyy-mm-dd')created_date,c.name_in_chinese chinese_name ,d.description position"
    sql +=
      " ,decode(a.status,'Y','已結案','N','未結案')status,decode(a.is_improve,'Y','是','否')is_improve,decode(b.assessment_result,'','暫未填寫',b.assessment_result)assessment_result"
    sql += " ,(select decode(count(*),2,'Y','N')  from xxhr_improve_achieve_flow z where z.docid = a.docid and a.current_flow = z.flow and z.flow_code <> 'end'"
    sql += ` and exists(select 1 from xxhr_improve_achieve_flow zz where zz.flow = 10 and zz.klm = '${decrypt(param.username)}' and zz.docid = z.docid`
    sql += ` or exists (select 1 from xxhr_improve_achieve_manager where account = '${decrypt(param.username)}')`
    sql += " ) ) as next_push_flag"
    sql +=
      " from xxhr_improve_achieve_header a,xxhr_improve_achieve_interview b,personnel_basic c,job_position d"
    sql +=
      " where a.docid = b.docid  and a.badge = c.no and c.pos_code = d.code"
    sql += ` and exists(select 1 from xxhr_improve_achieve_flow where docid = a.docid and klm = '${decrypt(
      param.username
    )}'
             or exists(select 1 from xxhr_improve_achieve_manager where account = '${decrypt(
               param.username
             )}'))`
    if (param.docid !== "") {
      sql += ` and a.docid = '${param.docid}'`
    }
    if (param.decade !== "") {
      sql += ` and to_char(a.created_date,'yyyymm') = '${param.decade}'`
    }
    if (param.badge !== "") {
      sql += ` and a.badge = '${param.badge}'`
    }
    if (param.org_code !== "") {
      sql += ` and c.area_code ='${param.org_code}'`
    }
    if (param.dept !== "") {
      sql += ` and c.dept_code = '${param.dept}'`
    }
    sql += ")x)"
    if (param.type !== "count") {
      sql += ` where rowseq between ${
        (param.current - 1) * param.pagesize + 1
      } and ${param.current * param.pagesize}`
    }
    callbackFn(res, oracle.connHrm, { sql })
  })

  router.post("/getflow", (req, res) => {
    const { docid } = req.body
    let sql =
      " select a.flow,a.is_multi,a.flow_phase,to_char(a.last_update_time,'yyyy-mm-dd hh24:mi') last_update_time,b.chinese_name,"
    sql += " case when a.is_multi = 'Y' and a.approve = 'Y' then '簽字'"
    sql += " when a.is_multi = 'Y' and a.approve <> 'Y' then '未簽字'"
    sql += " when a.is_multi = 'N' and a.approve = 'Y' then '同意'"
    sql += " when a.flow_code = 'end' then '結案'"
    sql += " else '退回' end as approve"
    sql += " from xxhr_improve_achieve_flow a,user_info_n b"
    sql += ` where a.klm = b.account(+) and a.docid = '${docid}'`
    sql += " order by flow"
    callbackFn(res, oracle.connHrm, { sql })
  })

  router.post("/getsignimg", (req, res) => {
    const { docid } = req.body
    let sql =
      " select case when sign_name = 'interview_sign' then '績效面談表簽字' || '--' || decode(sign_type,'A','主管','B','員工')"
    sql +=
      " when sign_name = 'improve_record_sign' then '第' || seq || '次改善計劃書改善內容簽字' || '--' || decode(sign_type,'A','主管','B','員工')"
    sql +=
      " else '第' || seq || '次改善計劃書改善結果簽字' || '--' || decode(sign_type,'A','主管','B','員工') end as sign_name,url"
    sql += "  from xxhr_improve_acheve_sign "
    sql += ` where docid ='${docid}'`
    callbackFn(res, oracle.connHrm, { sql })
  })

  router.post("/download", (req, res) => {
    const { filename } = req.body
    try {
      const filePath = "../uploadFile/hr_improve_achieve/sign/" + filename
      const fileData = fs.readFileSync(filePath, { encoding: "base64" })

      // 取得原始檔名
      const originalFileName = filename.split("-").pop()
      res.send({
        code: 1,
        data: {
          filename: originalFileName,
          content: fileData, // base64字串
        },
      })
    } catch (err){
      res.send({ code: 0, message: err })
    }
  })

  router.post("/uploadappendix", upload.single("file"), async (req, res) => {
    const { docid,username, filename } = req.body;
    if (filename && req.file?.filename) {
      //根據文件命名規則，文件被命名成了 時間戳 + -tempname + 後綴名的形式，這裏將tempname替換成為原始的文件
      //傳過來的文件名，只有文件名，不帶後綴
      //在改善表中可能會有多個簽名
      let filenameOnlyName = filename.split('.')[filename.split('.').length-2]
      let newfileName = req.file?.filename.replace("-tempname",docid + "_" + filenameOnlyName)
      fs.renameSync(
        "G:\\uploadFile\\hr_improve_achieve\\appendix\\" +
          req.file?.filename.toString(),
        "G:\\uploadFile\\hr_improve_achieve\\appendix\\" + newfileName
      );
      let sql = ` begin xxhr_improve_nextpush('${docid}','${decrypt(username)}','${newfileName}');end;`
      try {
        await oracle.connHrm({ sql });
        sql =
          " select count(*)count from xxhr_improve_achieve_header a,xxhr_improve_achieve_flow b ";
        sql += ` where a.docid = b.docid and a.current_flow = b.flow and a.docid ='${docid}'`;
        const resC = await oracle.connHrm({ sql });
        if (resC.code === 200 && resC.data[0].count === 1) {
          sql =
            " select b.docid,b.flow_phase,substr(b.klm,9) || '@compeq.huizhou.gd.cn' email,c.chinese_name ";
          sql +=
            " ,case when b.flow_code like '%_sign_%' then 'Y' else 'N' end as sign_flag";
          sql +=
            " from xxhr_improve_achieve_header a,xxhr_improve_achieve_flow b,user_info_n c";
          sql += ` where a.docid = b.docid and a.current_flow = b.flow and b.klm = c.account and a.docid = '${docid}'`;
          const resMail = await oracle.connHrm({ sql });
          await SendMailLoop(resMail);
          res.send({ code: 200, message: "success", data: [] });
        } else {
          res.send({ code: 200, message: "success", data: [] });
        }
      } catch (err) {
        res.send({ code: 0, message: err });
      }
    } else {
      res.send({ code: 200, message: "success", data: [] });
    }
  });

  return router
}
